#!/usr/bin/python3
# -*- coding: utf-8 -*-
# ======================================#
# @Author   : duanqizhong
# @time     : 2020-12-27 23:11
# @file     : stock_sample
# @Software : PyCharm
# ======================================#
import pandas as pd
from datetime import datetime
from src.core.stock import Stock
import sqlalchemy
from sqlalchemy import create_engine
from config import DATABASE_STOCK
import matplotlib.pyplot as plt
import math

# engine = create_engine("mysql+pymysql://{}:{}@{}/{}?charset={}".format('用户名', '登录密码', '127.0.0.1:3306', '数据库名','字符编码'))
engine = create_engine(f"mysql+pymysql://{DATABASE_STOCK['username']}:{DATABASE_STOCK['password']}@{DATABASE_STOCK['host']}:{DATABASE_STOCK['port']}/{DATABASE_STOCK['database']}?charset=utf8mb4")
mysql = engine.connect()

now=datetime.now()
datetime_std_date=datetime.strftime(now,"%Y-%m-%d")
datetime_int_date=datetime.strftime(now,"%Y%m%d")
datetime_str=datetime.strftime(now,"%Y%m%d%H%M")[2:]
year=datetime.strftime(now,"%Y")

def to_mysql(data,table):
    data.to_sql(table,con=mysql,dtype={'time': sqlalchemy.TIMESTAMP(timezone=True)},if_exists="append",index=False)

# 年均收益率
def compute_Avg_EarningRate(frequency="d",adjustflag="1"):
    """年平局收益率"""
    bs=Stock()
    def get_closeprice(code,start_date,end_date):
        """获取沪深A股历史K线数据"""
        rs_open=bs.history_k_data(code,"open",start_date=start_date,end_date=start_date,frequency=frequency,adjustflag=adjustflag)
        open_list=[]
        while rs_open.error_code=="0" and rs_open.next():
            # 获取一条记录，将记录合并在一起
            open_list.append(rs_open.get_row_data())
        result_open=pd.DataFrame(open_list,columns=rs_open.fields,index=[code])
        rs_close=bs.history_k_data(code,"close",start_date=end_date,end_date=end_date,frequency=frequency,adjustflag=adjustflag)
        close_list=[]
        while rs.error_code=="0" and rs_close.next():
            # 获取一条记录，将记录合并在一起
            close_list.append(rs_close.get_row_data())
        result_close=pd.DataFrame(close_list,columns=rs_close.fields,index=[code])
        result=result_open.join(result_close)
        return result

    rs = bs.stock_basic(code_name="银行")
    # rs = bs.stock_basic()
    result=pd.DataFrame()
    while rs.error_code=="0" and rs.next():
        # 获取一条记录，将记录合并在一起
        row_data=rs.get_row_data()
        code=row_data[0]
        ipoDate=row_data[2]
        outDate=row_data[3]
        if not outDate:
            continue
        df=get_closeprice(code,ipoDate,outDate)
        if result.empty:
            result=df
        else:
            result=result.append(df)

    result=result[result['open'] != ""]
    result["open"]=result["open"].astype(float)
    result["close"]=result["close"].astype(float)
    result["avgEarningRate"]=(result["close"]/result["open"]).apply(lambda x: math.pow(x,1/3)-1)
    result=result.sort_values(by=["avgEarningRate"],ascending=False)
    to_mysql(result, f"roe_avg_{datetime_str}")
    result[:10]["avgEarningRate"].plot(title="Avg Rarning Rate",kind="bar")
    plt.show()
    bs.logout()

# 净值产收益率 #todo 没结果
def compute_total_ROE(quarter=4):
    """ROE长期保持15%基本可以算作一个好公司"""
    bs = Stock()

    def compute_ROE(code, year, quarter):
        # 查询杜邦指数
        dupont_list = []
        rs_dupont = bs.dupont_data(code, year, quarter)
        while rs_dupont.error_code == "0" and rs_dupont.next():
            dupont_list.append(rs_dupont.get_row_data())
        result_profit = pd.DataFrame(dupont_list, columns=rs_dupont.fields)
        # 打印输出
        return result_profit

    # 获取全部证券基本资料
    rs = bs.stock_basic()
    rp = pd.DataFrame()
    while rs.error_code == "0" and rs.next():
        row_data=rs.get_row_data()
        code = row_data[0]
        ipoYear,ipoMonth,ipoDay=[int(r) for r in row_data[2].split("-")]
        if row_data[3]:
            outYear,outMonth,outDay=[int(r) for r in row_data[3].split("-")]
        else:
            outYear, outMonth, outDay=[int(r) for r in datetime_std_date.split("-")]

        for year in range(ipoYear, outYear):
            if year==outYear:
                quarters=range(1,int(outMonth/3)+1)
            elif year==ipoYear:
                quarters=range(int(ipoMonth/3),5)
            else:
                quarters=range(1,5)
            for quarter in quarters:
                df = compute_ROE(code, year, quarter)
                if df.empty:
                    continue
                else:
                    if rp.empty:
                        rp = df
                    else:
                        rp = rp.append(df)
    # 原始数据存储到数据库
    to_mysql(rp, f"roe_{year}")
    # 有用的数据
    result = rp[["code", "dupontROE"]]
    result = result[result["dupontROE"] != ""]
    result["dupontROE"] = result["dupontROE"].astype(float)
    series_mean = result.groupby(by=["code"])["dupontROE"].mean()
    series_std = result.groupby(by=["code"])["dupontROE"].std()
    df2 = pd.DataFrame({"mean": series_mean.data, "std": series_std.data}, columns=["mean", "std"],
                       index=series_mean.index)
    df2 = df2.sort_values(["mean"])
    to_mysql(df2, f"roe_total_{year}")

    # 退出
    bs.logout()


# 市现率
def pcfNcfTTM():
    """市现率：每股股价/每股现金流"""
    bs=Stock()
    rs=bs.all_stock(day=datetime_std_date)
    code_list=[]
    while rs.error_code=="0" and rs.next():
        code_list.append(rs.get_row_data()[0])
    df=pd.DataFrame()
    for code in code_list:
        rs=bs.history_k_data(code,"date,code,pcfNcfTTM",start_date=datetime_std_date,end_date=datetime_std_date,frequency="d",adjustflag="3")
        if rs.error_code==0:
            result=rs.get_data()
            n=result.shape[0]
            if n<0: continue
            # 删除pe,pcf,pb为0的证券或指数
            if float(result.iloc[0,2])!=0:
                if df.empty:
                    df=result
                else:
                    df=pd.concat([df,result])
    print(df)
    to_mysql(df,f"pcf_{datetime_int_date}")
    df['pcfNcfTTM']=df['pcfNcfTTM'].astype(float)
    # 以pcfNcfTTM进行升序排序
    df_sortby_pcfNcfTTM=df.sort_values(by='pcfNcfTTM')
    to_mysql(df_sortby_pcfNcfTTM, f"pcfNcfTTM_{datetime_int_date}")

    bs.logout()

# 市盈率：值越低越好
def per():
    """市盈率：估价/每股盈余*100"""
    bs=Stock()
    rs=bs.all_stock(day=datetime_std_date)
    code_list=[]
    while rs.error_code=="0" and rs.next():
        code_list.append(rs.get_row_data()[0])

    df=pd.DataFrame()
    for code in code_list:
        rs=bs.history_k_data(code,"date,code,peTTM,pbMRQ,pcfNcfTTM",start_date=datetime_std_date,end_date=datetime_std_date,frequency="d",adjustflag="3")
        if rs.error_code=="0":
            result=rs.get_data()
            n=result.shape[0]
            if n<=0: continue
            # 删除pe,pcf,pb为0的证券或指数
            if float(result.iloc[0,2])!=0:
                if df.empty:
                    df=result
                else:
                    df=pd.concat([df,result])
    to_mysql(df,f"pcf_{datetime_int_date}")

    df["peTTM"]=df["peTTM"].astype(float)
    # 以peTTM进行升序排序
    df_sortby_peTTM=df.sort_values(by="peTTM")
    to_mysql(df_sortby_peTTM, f"peTTM_{datetime_int_date}")

    bs.logout()

# 市净率
def pb():
    """市净率：每股股价/每股净值产"""
    bs=Stock()
    rs=bs.all_stock(day=datetime_std_date)
    code_list=[]
    while rs.error_code=="0" and rs.next():
        code_list.append(rs.get_row_data()[0])

    df=pd.DataFrame()
    for code in code_list:
        rs=bs.history_k_data(code,"date,code,pbMRQ",start_date=datetime_std_date,end_date=datetime_std_date,frequency="d",adjustflag="3")
        if rs.error_code=="0":
            result=rs.get_data()
            n=result.shape[0]
            if n<=0: continue
            # 删除pbMRQ为0的证券或指数
            if float(result.iloc[0,2])!=0:
                if df.empty:
                    df=result
                else:
                    df=pd.concat([df,result])

    to_mysql(df,f"pb_{datetime_int_date}")

    bs.logout()

# 大阳线和大阴线
def kline_application(code,start_dte,end_date,N=3):

    def judge_function(open,close):
        if open>close:
            return 0
        else:
            return 1

    def judge_kline_category(code,start_date,end_date):
        """判断证券在起始时间内的每日K线类别：阳线，阴线"""
        bs=Stock()
        rs=bs.history_k_data(code,"date,code,open,high,low,close,tradeStatus",start_date=start_date,end_date=end_date,frequency="d",adjustflag="3")
        result_list=[]
        while rs.error_code=="0" and rs.next():
            result_list.append(rs.get_row_data())
        df_init=pd.DataFrame(result_list,columns=rs.fields)
        # 删除停盘数据
        df_status=df_init[df_init["tradeStatus"]=="1"]
        df_status["open"]=df_status["open"].astype(float)
        df_status["high"] = df_status["high"].astype(float)
        df_status["low"] = df_status["low"].astype(float)
        df_status["close"] = df_status["close"].astype(float)
        df_status["kline_category"] = df_status.apply(lambda x:judge_function(x.open,x.close),axis=1)
        to_mysql(df_status,f"kline_{datetime_int_date}")
        return df_status

    df=judge_kline_category(code,start_date=start_dte,end_date=end_date)
    daycounts=df.shape[0]   #行数
    df["kline_numb"]=[x for x in df["kline_category"]]
    df["scene"]=0
    total_counts_up=0
    total_counts_up_sum=0
    total_counts_down=0
    total_counts_down_sum=0
    for i in range(0,daycounts-N-1):
        kline_numb_counts=0
        for j in range(0,N):
            kline_numb_counts+=df.iloc[i+j,8]
        if kline_numb_counts==N and df.iloc[i+N,8]==0:
            # N天后出现大阴线
            total_counts_down+=1
            if df.iloc[i+N+1,2]<df.iloc[i+N,5]:
                total_counts_down_sum+=1
                df.iloc[i+N,9]=2
        if kline_numb_counts==0 and df.iloc[i+N,8]==1:
            # N天后出现大阳线
            total_counts_up+=1
            if df.iloc[i+N+1,2]>df.iloc[i+N,5]:
                total_counts_up_sum+=1
                df.iloc[i+N,9]=1
    to_mysql(df,f"kline_{datetime_int_date}")
    print(f"证券代码：{df['code'][0]}")
    print(f"证券股价连续 {N} 天下跌后出现 大阳线 的总次数：{total_counts_up}")
    print(f"证券股价连续 {N} 天下跌后出现大阳线，次日股价开盘上涨的次数: {total_counts_up_sum}, 占比：{total_counts_up/total_counts_up_sum}")
    print(f"证券股价连续 {N} 天上涨后出现 大阴线 的总次数：{total_counts_down}")
    print(f"证券股价连续 {N} 天上涨后出现大阴线，次日股价开盘下跌的次数: {total_counts_down_sum}, 占比：{total_counts_down / total_counts_down_sum}")

    return total_counts_up,total_counts_up_sum,total_counts_down,total_counts_down_sum


# 日线CCI
def cci():
    """超卖超买"""
    pass

def rsi():
    pass

def macd():
    pass



# compute_total_ROE()
compute_Avg_EarningRate()